/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import java.sql.*;
import java.util.Vector;

/**
 *
 * @author Venus
 */
public class PhiPV_Model {

    public Vector LayDSPPV() {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from phiphucvu  ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.PhiPV ppv = new Beans.PhiPV();
                ppv.setMaKV(rss.getInt(1));
                ppv.setMaTD(rss.getInt(2));
                ppv.setPhiPV(rss.getInt(3));
                v.add(ppv);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean TaoPhiPV(String makv, String matd, String servicecharge) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " insert into phiphucvu values "
                    + " (" + makv + ", " + matd + ", " + servicecharge + ") ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector SortTenKVPPV(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from phiphucvu A, khuvuc B where A.MaKV = B.MaKV and MaNN = " + langid + " Order by TenKV " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.PhiPV ppv = new Beans.PhiPV();
                ppv.setMaKV(rss.getInt(1));
                ppv.setMaTD(rss.getInt(2));
                ppv.setPhiPV(rss.getInt(3));
                v.add(ppv);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTenTDPPV(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from phiphucvu A, thoidiem B where A.MaTD = B.MaTD and MaNN = " + langid + " Order by TenTD " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.PhiPV ppv = new Beans.PhiPV();
                ppv.setMaKV(rss.getInt(1));
                ppv.setMaTD(rss.getInt(2));
                ppv.setPhiPV(rss.getInt(3));
                v.add(ppv);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortPPV(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from phiphucvu Order by PhiPV " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.PhiPV ppv = new Beans.PhiPV();
                ppv.setMaKV(rss.getInt(1));
                ppv.setMaTD(rss.getInt(2));
                ppv.setPhiPV(rss.getInt(3));
                v.add(ppv);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SearchSerCharge(String tenkv, String tentd, String ppv, int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(A.MaKV), MaTD, PhiPV "
                    + " from  phiphucvu A, khuvuc B  "
                    + " where A.MaKV = B.MaKV   ";
            String[] arr = {tenkv, tentd, ppv};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenkv)) {
                    query = query + " and A.MaKV = " + tenkv + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tentd)) {
                    query = query + " and MaTD = " + tentd + "  ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(ppv)) {
                    query = query + " and PhiPV = " + ppv + " ";
                }
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.PhiPV pv = new Beans.PhiPV();
                pv.setMaKV(rss.getInt(1));
                pv.setMaTD(rss.getInt(2));
                pv.setPhiPV(rss.getInt(3));
                v.add(pv);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTenKVPPV(String tenkv, String tentd, String ppv, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(A.MaKV), MaTD, PhiPV "
                    + " from  phiphucvu A, khuvuc B  "
                    + " where A.MaKV = B.MaKV   ";
            String[] arr = {tenkv, tentd, ppv};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenkv)) {
                    query = query + " and A.MaKV = " + tenkv + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tentd)) {
                    query = query + " and MaTD = " + tentd + "  ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(ppv)) {
                    query = query + " and PhiPV = " + ppv + " ";
                }
            }
            query = query + " order by TenKV " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.PhiPV pv = new Beans.PhiPV();
                pv.setMaKV(rss.getInt(1));
                pv.setMaTD(rss.getInt(2));
                pv.setPhiPV(rss.getInt(3));
                v.add(pv);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTenTDPPV(String tenkv, String tentd, String ppv, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(A.MaKV), A.MaTD, PhiPV "
                    + " from  phiphucvu A, khuvuc B, thoidiem C  "
                    + " where A.MaKV = B.MaKV and A.MaTD = C.MaTD  ";
            String[] arr = {tenkv, tentd, ppv};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenkv)) {
                    query = query + " and A.MaKV = " + tenkv + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tentd)) {
                    query = query + " and MaTD = " + tentd + "  ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(ppv)) {
                    query = query + " and PhiPV = " + ppv + " ";
                }
            }
            query = query + " order by TenTD " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.PhiPV pv = new Beans.PhiPV();
                pv.setMaKV(rss.getInt(1));
                pv.setMaTD(rss.getInt(2));
                pv.setPhiPV(rss.getInt(3));
                v.add(pv);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchPPV(String tenkv, String tentd, String ppv, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(A.MaKV), MaTD, PhiPV "
                    + " from  phiphucvu A, khuvuc B  "
                    + " where A.MaKV = B.MaKV   ";
            String[] arr = {tenkv, tentd, ppv};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenkv)) {
                    query = query + " and A.MaKV = " + tenkv + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tentd)) {
                    query = query + " and MaTD = " + tentd + "  ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(ppv)) {
                    query = query + " and PhiPV = " + ppv + " ";
                }
            }
            query = query + " order by PhiPV " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.PhiPV pv = new Beans.PhiPV();
                pv.setMaKV(rss.getInt(1));
                pv.setMaTD(rss.getInt(2));
                pv.setPhiPV(rss.getInt(3));
                v.add(pv);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean EditPPV(String ppv, String makv, String matd) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update phiphucvu set PhiPV = " + ppv + " where MaKV = " + makv + " and MaTD = " + matd + " ";
            int n = stmt.executeUpdate(query);
            stmt.close();

            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean Check(String makv, String matd) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from phiphucvu where MaKV = " + makv + " and MaTD = " + matd + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static int LoadPPV(String makv, String matd) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        int n = 0;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();

            String query = "select * from phiphucvu where MaKV = " + makv + " and MaTD = " + matd + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = rss.getInt(3);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return n;
    }
}
